<?php

class Reservation_model extends CI_Model {

    function __construct() {
        parent::__construct();
    }

	function get_guest ($rev_id){
	$sql ="
	SELECT r.resv_id, g.guest_name, g.guest_address, r.resv_adult_number,r.resv_child_number, 
	r.resv_infant_number, u.emp_name
	FROM reservation as r, guest as g, user as u
	WHERE g.guest_id = r.guest_id AND u.user_id = r.user_id AND r.deleted = 'No' 
	  AND g.deleted = 'No' AND	r.resv_id ='" .$rev_id."'";
	
	return  $this->db->query($sql);
	}
	
	function get_room ($rev_id){
	$sql ="
	SELECT ro.room_number,DATE_FORMAT(rr.booking_time,'%d-%m-%Y %T') as booking, 
	 DATE_FORMAT(rr.check_in_time,'%d-%m-%Y %T') as check_in, 
	 DATE_FORMAT(rr.check_out_time,'%d-%m-%Y %T') as check_out, rr.price
	FROM reservation as r, reservation_room as rr, room as ro
	WHERE r.resv_id = rr.resv_id AND rr.room_id = ro.room_id AND rr.deleted = 'No' AND
	r.deleted = 'No' AND ro.deleted = 'No' AND r.resv_id ='" .$rev_id."'";
	return  $this->db->query($sql);
	}
	
	function get_material($rev_id){
	$sql ="
	SELECT  DATE_FORMAT(rm.resv_mat_time,'%d-%m-%Y %T') as mat_time , m.material_code, m.material_name, FORMAT(rm.resv_mat_qty,0) as mat_qty,
	rm.resv_mat_price, (rm.resv_mat_qty * rm.resv_mat_price) AS pricematerial
	FROM reservation as r, reservation_room as rr, reservation_material as rm, material as m
	WHERE r.resv_id = rr.resv_id AND rr.resv_room_id = rm.resv_room_id AND m.material_id = rm.material_id AND
	rm.deleted = 'No' AND r.resv_id ='" .$rev_id."'";
	return $this->db->query($sql);
	
	}
	
	function total_reservation($rev_id){
	$sql="
	SELECT SUM(rr.price) as subtotalroom, r.ppn_tax, r.service_tax, r.discount_nominal,
	r.discount_percent, r.extra_charge_nominal, r.extra_charge_percent  
	FROM reservation as r, reservation_room as rr
	WHERE r.resv_id = rr.resv_id AND rr.deleted = 'No' AND r.resv_id ='" .$rev_id."'";
	return  $this->db->query($sql);
	
	}
	function total_material($rev_id){
	$sql="
	SELECT SUM(rm.resv_mat_qty*rm.resv_mat_price) as subtotmat
	FROM reservation as r, reservation_material as rm, reservation_room as rr
	WHERE r.resv_id = rr.resv_id AND rr.resv_room_id = rm.resv_room_id AND
	r.deleted = 'No' AND rr.deleted = 'No' AND rm.deleted = 'No' AND r.resv_id ='" .$rev_id."'";
	return  $this->db->query($sql);
	
	}
	
	function get_service ($rev_id)
	{
		$sql = "
		SELECT so.room_name, DATE_FORMAT(so.serv_ord_time,'%d-%m-%Y %T') as tanggal,
 s.service_code, s.service_name, FORMAT(sd.serv_det_qty,0) as qty, sd.serv_det_price,
(sd.serv_det_qty * sd.serv_det_price) as total
FROM service as s, service_detail as sd, service_order as so , `user` as u, reservation as r
WHERE u.user_id = r.user_id AND r.resv_id = so.resv_id AND so.serv_ord_id = sd.serv_ord_id AND sd.service_id = s.service_id 
and so.deleted = 'No' and so.bill_type = 'Reservation' AND r.resv_id ='".$rev_id."'";
		return $this->db->query($sql);
	}
	function total_service ($rev_id)
	{
		$sql= "
		SELECT Sum(sd.serv_det_qty * sd.serv_det_price) as subtotal,
		so.discount_percent,
		so.service_tax,
		so.ppn_tax,
		so.discount_nominal,
		so.serv_ord_total,
		so.serv_ord_paid
		FROM service_detail as sd, service_order as so, reservation as r 
		WHERE so.serv_ord_id = sd.serv_ord_id AND 
		so.resv_id = r.resv_id and r.deleted = 'No' and
		so.deleted = 'No' AND so.bill_type = 'Reservation' and
		r.resv_id ='".$rev_id."'";
		return $this->db->query($sql);
	}
	
	function transaction ($rev_id)
	{
	$sql= "
	SELECT p.payment_name, rp.resv_paym_total, rp.resv_paym_desc, rp.special_code
	FROM reservation as r, reservation_payment as rp, payment_type as p
	WHERE r.resv_id = rp.resv_id AND p.payment_id = rp.payment_id AND
	r.deleted = 'No' AND rp.deleted = 'No' AND p.deleted = 'No' AND
	r.resv_id ='".$rev_id."'";
	return $this->db->query($sql);
	
	}
	
	function tottransaction ($rev_id)
	{
	$sql= "
	SELECT  sum(rp.resv_paym_total) as total
	FROM reservation as r, reservation_payment as rp
	WHERE r.resv_id = rp.resv_id AND
	r.deleted = 'No' AND rp.deleted = 'No' AND
	r.resv_id ='".$rev_id."'";
	return $this->db->query($sql);
	
	}
	
	
	
	
    function roomSearch123($checkin, $checkout, $adult, $child, $infant) {
        $sql = "SELECT a.room_number, e.room_type_name, e.room_type_price, e.room_type_max_adult, e.room_type_max_child, e.room_type_max_infant FROM room a
            LEFT JOIN (select c.resv_room_id, b.room_number, c.resv_room_date from reservation b 
            join reservation_room c on b.resv_id = c.resv_id) d ON (a.room_number = d.room_number 
            AND d.resv_room_date BETWEEN '" . $checkin . "' AND '" . $checkout . "') 
            left join room_type e on a.room_type_id = e.room_type_id
                WHERE d.resv_room_id IS NULL and e.room_type_max_adult >= 0 
                and e.room_type_max_child >= 0 and e.room_type_max_infant >= 0
                    ORDER BY a.room_number asc";

        $return = $this->db->query($sql);

        return $return->result_array();
    }
	
	function roomSearch($date) {
        $sql = "select r.room_id, r.room_number, rt.room_type_name, rt.room_type_price, r.room_type_id
					from room as r left join room_type as rt on r.room_type_id = rt.room_type_id
					where room_id not in 
					(SELECT rr.room_id
					FROM room as r left join reservation_room as rr on rr.room_id = r.room_id 
					WHERE (resv_status = 'Check In' or resv_status = 'Booked' or resv_status = 'Out Of Order') and resv_room_date = ' ".$date." ' )";
		$return = $this->db->query($sql);

        return $return->result_array();
    }
	
	function select_rate_type($id, $date){
		$sql = "select * 
					from room as r left join room_type as rt on rt.room_type_id=r.room_type_id left join room_rate as rr on rr.room_type_id=rt.room_type_id left join rate_type as rat on rat.rate_type_id=rr.rate_type_id 
					where r.room_id=".$id." and rr.room_rate_date ='".$date."' and rr.deleted = 'no'";
		$return = $this->db->query($sql);

        return $return->result_array();
	}
	
	function select_rate_price($rate_id, $room_type_id, $date){
		$sql = "select rrate.room_rate_price 
					from room_rate as rrate where rrate.rate_type_id =".$rate_id." and rrate.room_type_id =".$room_type_id." and rrate.room_rate_date = '".$date."'";
		$return = $this->db->query($sql);

        return $return->row_array();
	}

    function roomPrice($checkin, $checkout, $room_number) {
        $sql = "select a.room_number, b.room_type_name, b.room_type_price, c.rate_type_name, c.rate_type_id, d.room_rate_date, d.room_rate_price from room a 
left join room_type b on b.room_type_id = a.room_type_id
left join rate_type c on c.room_type_id = b.room_type_id
left join room_rate d on d.rate_type_id = c.rate_type_id
and d.room_rate_date between '" . $checkin . "' and '" . $checkout . "' where a.room_number = " . $room_number;

        $return = $this->db->query($sql);

        return $return->result_array();
    }
	
	function insert_resv_room1($tanggal, $status, $room, $price, $date){
		$sess = $this->session->userdata('logged_in');
		
		$data = array('resv_room_date'=>$tanggal, 'resv_status'=>$status, 'room_id'=>$room, 'price'=>$price, 'booking_time'=>$date, 'last_modifier'=>$sess['user_id']);
		$this->db->insert('reservation_room', $data);
		$last_id = $this->db->insert_id();
		return $last_id;
	}
	
	function insert_resv_room2($tanggal, $status, $room, $price, $date){
		$sess = $this->session->userdata('logged_in');
		$data = array('resv_room_date'=>$tanggal, 'resv_status'=>$status, 'room_id'=>$room, 'price'=>$price, 'check_in_time'=>$date, 'last_modifier'=>$sess['user_id']);
		$this->db->insert('reservation_room', $data);
		$last_id = $this->db->insert_id();
		return $last_id;
	}
	
	function insert_new_room1($tanggal, $status, $room, $price, $date, $resv_id){
		$sess = $this->session->userdata('logged_in');
		
		$data = array('resv_id'=>$resv_id, 'resv_room_date'=>$tanggal, 'resv_status'=>$status, 'room_id'=>$room, 'price'=>$price, 'booking_time'=>$date, 'last_modifier'=>$sess['user_id']);
		$this->db->insert('reservation_room', $data);
		$last_id = $this->db->insert_id();
		return $last_id;
	}
	
	function insert_new_room2($tanggal, $status, $room, $price, $date, $resv_id){
		$sess = $this->session->userdata('logged_in');
		
		$data = array('resv_id'=>$resv_id, 'resv_room_date'=>$tanggal, 'resv_status'=>$status, 'room_id'=>$room, 'price'=>$price, 'check_in_time'=>$date, 'last_modifier'=>$sess['user_id']);
		$this->db->insert('reservation_room', $data);
		$last_id = $this->db->insert_id();
		return $last_id;
	}
	
	function insert_resv_rate_room1($tanggal, $status, $room, $price, $date, $rate_id){
		$sess = $this->session->userdata('logged_in');
		$data = array('resv_room_date'=>$tanggal, 'rate_type_id'=>$rate_id, 'resv_status'=>$status, 'room_id'=>$room, 'price'=>$price, 'booking_time'=>$date, 'last_modifier'=>$sess['user_id']);
		$this->db->insert('reservation_room', $data);
		$last_id = $this->db->insert_id();
		return $last_id;
	}
	
	function insert_resv_rate_room2($tanggal, $status, $room, $price, $date, $rate_id){
		$sess = $this->session->userdata('logged_in');
		$data = array('resv_room_date'=>$tanggal, 'rate_type_id'=>$rate_id, 'resv_status'=>$status, 'room_id'=>$room, 'price'=>$price, 'check_in_time'=>$date, 'last_modifier'=>$sess['user_id']);
		$this->db->insert('reservation_room', $data);
		$last_id = $this->db->insert_id();
		return $last_id;
	}
	
	function insert_Room($room_id, $date){
		$sess = $this->session->userdata('logged_in');
		$data = array('room_id'=>$room_id, 'resv_room_date'=>$date,'resv_status'=>'Booked', 'last_modifier'=>$sess['user_id']);
		$this->db->insert('reservation_room', $data);
		$last_id = $this->db->insert_id();
		return $last_id;
	}
	
	function resv_room_id($id){
		$sql = "SELECT rr.resv_room_id,rr.resv_status, r.room_number, rr.price
					from reservation_room as rr join room as r
					where rr.room_id = r.room_id and rr.resv_room_id =".$id;
		$hasil = $this->db->query($sql);
		return $hasil->row_array();
	}
	
	function select_resv_room($id){
		$sql = "SELECT rr.resv_room_date, rr.resv_room_id, r.room_id, r.room_type_id, rt.room_type_price, rt.room_type_name, r.room_number
					from reservation_room as rr 
					join room as r left join room_type as rt on r.room_type_id = rt.room_type_id
					where rr.room_id = r.room_id and rr.resv_room_id =".$id;
		$hasil = $this->db->query($sql);
		return $hasil->result_array();
	}
	
	function resv_new_room_id($id){
		$sql = "SELECT rr.resv_room_id, rr.resv_id, r.room_number, rt.room_type_name, rate.rate_type_name, rr.resv_room_date, rr.price, rr.resv_status, rr.booking_time, rr.check_in_time, rr.check_out_time
					FROM reservation_room as rr left join rate_type as rate on rr.rate_type_id = rate.rate_type_id
					JOIN room as r, room_type as rt
					WHERE rr.room_id = r.room_id and r.room_type_id = rt.room_type_id and rr.resv_room_id =".$id;

        $hasil = $this->db->query($sql);

        return $hasil->row_array();
	}

    function saveResv($staff) {
        $data = array('user_id'=>$staff, 'guest_id'=>$_POST['guest_id'], 'resv_adult_number'=>$_POST['adult'], 'resv_child_number'=>$_POST['child'], 'resv_infant_number'=>$_POST['infant'], 'resv_type'=>$_POST['resv_type'], 'last_modifier'=>$staff);
        $this->db->insert('reservation', $data);
        $last_resv = $this->db->insert_id();
        return $last_resv;

        /*if (empty($_POST['rate_type'][0])) {
            $_POST['rate_type'][0] = 0;
        }


        $sql2 = "INSERT INTO `reservation_room` (`resv_id`, `resv_room_date`, `rate_type_id`, `price`) 
            VALUES (" . $lastResv[0]['resv_id'] . ", '" . $_POST['tanggal'][0] . "', " . $_POST['rate_type'][0] . ", " . $_POST['price'][0] . ")";

        if ($_POST['tanggal'][1]) {
            for ($i = 1; $i < count($_POST['tanggal']); $i++) {
                if (empty($_POST['rate_type'][$i])) {
                    $_POST['rate_type'][$i] = 0;
                }

                $sql2 .= ", (" . $lastResv[0]['resv_id'] . ", '" . $_POST['tanggal'][$i] . "', " . $_POST['rate_type'][$i] . ", " . $_POST['price'][$i] . ") ";
            }
        }

        $this->db->query($sql2);*/
    }
	
	function update_resv_room_id_RB($id_resv, $resv_room, $status, $rate, $rate_price, $time){
		$this->db->where('resv_room_id', $resv_room);
		$this->db->set('resv_id', $id_resv);
		$this->db->set('rate_type_id', $rate);
		$this->db->set('price', $rate_price);
		$this->db->set('resv_status', $status);
		$this->db->set('booking_time', $time);
		$this->db->update('reservation_room');
	}
	
	function update_resv_room_id_B($id_resv, $resv_room, $status, $rate_price, $time){
		$this->db->where('resv_room_id', $resv_room);
		$this->db->set('resv_id', $id_resv);
		$this->db->set('price', $rate_price);
		$this->db->set('resv_status', $status);
		$this->db->set('booking_time', $time);
		$this->db->update('reservation_room');
	}
	
	function update_resv_room_id_RCI($id_resv, $resv_room, $status, $rate, $rate_price, $time){
		$this->db->where('resv_room_id', $resv_room);
		$this->db->set('resv_id', $id_resv);
		$this->db->set('rate_type_id', $rate);
		$this->db->set('price', $rate_price);
		$this->db->set('resv_status', $status);
		$this->db->set('check_in_time', $time);
		$this->db->update('reservation_room');
	}
	
	function update_resv_room_id_CI($id_resv, $resv_room, $status, $rate_price, $time){
		$this->db->where('resv_room_id', $resv_room);
		$this->db->set('resv_id', $id_resv);
		$this->db->set('price', $rate_price);
		$this->db->set('resv_status', $status);
		$this->db->set('check_in_time', $time);
		$this->db->update('reservation_room');
	}
	
	function update_resv_room_id_OOR($id_resv, $resv_room, $status, $rate, $rate_price, $time){
		$this->db->where('resv_room_id', $resv_room);
		$this->db->set('resv_id', $id_resv);
		$this->db->set('rate_type_id', $rate);
		$this->db->set('price', $rate_price);
		$this->db->set('resv_status', $status);
		$this->db->update('reservation_room');
	}
	
	function update_resv_room_id_OO($id_resv, $resv_room, $status, $rate_price, $time){
		$this->db->where('resv_room_id', $resv_room);
		$this->db->set('resv_id', $id_resv);
		$this->db->set('price', $rate_price);
		$this->db->set('resv_status', $status);
		$this->db->update('reservation_room');
	}
	
	function cancel_room($id){
	$this->db->where('resv_room_id', $id);
	$this->db->delete('reservation_room');
	}
	
	function cancel_room_ordered($id){
	$this->db->where('resv_room_id',$id);
	$this->db->set('deleted', 'Yes');
	$this->db->update('reservation_room');
	}
	
	function checkin_room($id, $time){
	$this->db->where('resv_room_id',$id);
	$this->db->set('resv_status', 'Check In');
	$this->db->set('check_in_time', $time);
	$this->db->update('reservation_room');
	}
	
	function checkout_room($id, $time){
	$this->db->where('resv_room_id',$id);
	$this->db->set('resv_status', 'Check Out');
	$this->db->set('check_out_time', $time);
	$this->db->update('reservation_room');
	}
	
	function last_id(){
		$query = "select max(resv_id) as max from reservation";
		$result = $this->db->query($query)->row();
		$string = (int) $result->max;
		return $string;
	}

    function getResv($resv_id) {
        $sql = "SELECT  resv_id, c.guest_name,  resv_status,  resv_checkin,  resv_checkout,  
            resv_adult_number,  resv_child_number,  resv_infant_number 
            FROM reservation a
            left join guest c on c.guest_id = a.guest_id
            where a.resv_id = " . $resv_id;
        $result = $this->db->query($sql);

        return $result->row_array();
    }

    function editResv() {
        $sql = "UPDATE `reservation` SET `resv_status` = " . $_POST['status'] . ", 
            `resv_checkout` = '" . $_POST['checkout'] . 
                "', `resv_adult_number` = " . $_POST['adult'] . ", `resv_child_number` = " . $_POST['child'] . "
                    , `resv_infant_number` = " . $_POST['infant'] . " WHERE `resv_id`=" . $_POST['resv_id'];
        $this->db->query($sql);
    }

    function editRoom($id) {
        $sql = "SELECT rr.room_id, rr.resv_room_id, rr.resv_id, r.room_number, rt.room_type_name, rate.rate_type_name, rr.resv_room_date, rr.price, rr.resv_status, rr.booking_time, rr.check_in_time, rr.check_out_time, rr.deleted, DATE(now()) as now
					FROM reservation_room as rr left join rate_type as rate on rr.rate_type_id = rate.rate_type_id
					JOIN room as r, room_type as rt
					WHERE rr.room_id = r.room_id and r.room_type_id = rt.room_type_id and rr.resv_id =". $id;

        $hasil = $this->db->query($sql);

        return $hasil->result_array();
    }

    function listService($id) {
        $sql = "select resv_serv_id, resv_id,service_name, resv_serv_unit, resv_serv_total, resv_serv_desc
from reservation_service a 
join service b on b.service_id = a.service_id
where resv_id =" . $id;

        $sql = $this->db->query($sql);

        return $sql->result_array();
    }

    function listServ($resv_id) {
        $this->db->select('so.*,g.*,u.emp_name');
		$this->db->from('service_order as so');
		$this->db->join('reservation as r', 'r.resv_id=so.resv_id', 'left');
		$this->db->join('guest as g', 'g.guest_id=r.guest_id', 'left');
		$this->db->join('user as u', 'u.user_id=so.user_id', 'left');
		$this->db->where('so.resv_id', $resv_id);
		$this->db->where('so.deleted', 'no');
		
		return $this->db->get();
    }
	
	function serv_det($serv_ord_id) {
		$this->db->select();
		$this->db->from('service_detail as sd');
		$this->db->join('service as s', 's.service_id=sd.service_id', 'left');
		$this->db->where('sd.serv_ord_id', $serv_ord_id);
		$this->db->where('sd.deleted', 'no');
		
		return $this->db->get();
	}

    function addServ() {
        $sql = "INSERT INTO reservation_service (`resv_id`, `service_id`, `resv_serv_unit`, `resv_serv_total`, `resv_serv_desc`, `resv_serv_date`) 
            VALUES (" . $_POST['resv_id'] . ", " . $_POST['service_id'] . ", " . $_POST['jumlah'] . ", " . $_POST['total'] . ", '" . $_POST['desc'] . "', NOW());";
        $this->db->query($sql);
    }
    
    function getServ($id) {
        $sql = "SELECT  resv_serv_id, `service_id`, `resv_serv_unit`, `resv_serv_total`, `resv_serv_desc` FROM `reservation_service` where resv_serv_id =".$id;

        $sql = $this->db->query($sql);

        return $sql->row_array();
    }
    
    function editServ() {
        $sql = "UPDATE `reservation_service` SET `service_id` = " . $_POST['service_id'] . ", `resv_serv_unit` = '" . $_POST['jumlah'] . "',  `resv_serv_date` = " . $_POST['date'] . ",`resv_serv_total` = " . $_POST['total'] . ", `resv_serv_desc` = '" . $_POST['desc'] . "' WHERE `resv_serv_id`=" . $_POST['resv_serv_id'];
        $this->db->query($sql);
    }

    function deleteServ($id) {
        $sql = "DELETE FROM `reservation_service` where resv_serv_id = " . $id;
        $this->db->query($sql);
    }
    
    function getBiaya($id){
        $sql = "select resv_id, sum(price) as total_room, sum(total) as total_service, (sum(price)+sum(total)) as total from (select resv_id, price, '' as total
from reservation_room 
union all
select resv_id, '' as price, resv_serv_total
from reservation_service ) a
where a.resv_id = ".$id;
        
        $sql = $this->db->query($sql);
        
        return $sql->result_array();
    }
	
	function getBiaya2($id){
        $sql = "select resv_id, sum(price) as total_room, sum(total) as total_service, (sum(price)+sum(total)) as total from (select resv_id, price, '' as total
from reservation_room 
union all
select resv_id, '' as price, resv_serv_total
from reservation_service ) a
where a.resv_id = ".$id;
        
        return $this->db->query($sql);
    }
	
	function getBiayatax($id){
       $sql = "select resv_id, sum(price) as total_room, sum(total) as total_service, ((sum(price)+sum(total)) + ((sum(price)+sum(total))*0.1) ) as total 
from (select resv_id, price, '' as total
from reservation_room 
union all
select resv_id, '' as price, resv_serv_total
from reservation_service ) a
where a.resv_id = ".$id;
        
        return $this->db->query($sql);
    }
	
	
    
    function listPayment() {
       $this->db->select();
	   $this->db->from('payment_type');
	   $this->db->where('deleted', 'no');
	   
	   return $this->db->get();
    }
    
    function getPayment($id) {
        $sql = "select a.resv_paym_id, resv_id,payment_name,  resv_paym_total, resv_paym_desc
from reservation_payment a 
join payment_type b on b.payment_id = a.payment_id
where resv_id =".$id;

        $sql = $this->db->query($sql);

        return $sql->result_array();
    }
    
    function addPayment($data) {
		$this->db->insert('reservation_payment', $data);
		
		return $this->db->insert_id();
    }
    
    function getEditPaym($id) {
        $sql = "SELECT  resv_paym_id, `payment_id`, `resv_paym_total`, `resv_paym_desc` FROM `reservation_payment` where resv_paym_id =".$id;

        $sql = $this->db->query($sql);

        return $sql->row_array();
    }
    
    function editPayment() {
        $sql = "UPDATE `reservation_payment` SET `payment_id` = " . $_POST['payment_id'] . ",  `resv_paym_total` = " . $_POST['jumlah'] . ", `resv_paym_desc` = '" . $_POST['desc'] . "' WHERE `resv_paym_id`=" . $_POST['resv_paym_id'];
        $this->db->query($sql);
    }

    function delPayment($id) {
        $sql = "DELETE FROM `reservation_payment` where resv_paym_id = " . $id;
        $this->db->query($sql);
    }
    
	function extendCheckOut(){
	$sql = "select a.room_number, a.resv_checkout, c.room_type_price from reservation a
join room b on a.room_number = b.room_number
join room_type c on b.room_type_id = c.room_type_id
where resv_id = ". $_POST['resv_id'];
$query = $this->db->query($sql);

$hasil = $query->row_array();
	$sql2 = "INSERT INTO `reservation_room` (`resv_id`, `resv_room_date`, `rate_type_id`, `price`) 
            VALUES (" . $_POST['resv_id'] . ", '" . $hasil['resv_checkout']. "', 0, " . $hasil['room_type_price'] . ")";
	
        if ($_POST['date_change']>1) {
			for($i=1; $i<$_POST['date_change']; $i++){
                $sql2 .= ", (" . $_POST['resv_id'] . ", '" . date('Y-m-d', strtotime($hasil['resv_checkout']. ' + '.$i.' days')). "', 0, " . $hasil['room_type_price'] . ") ";
            }
		}
		$this->db->query($sql2);
	}
	
	function get_resv_in_time($date) {
		$this->db->select('*');
		$this->db->from('reservation');
		$this->db->join('reservation_room', 'reservation_room.resv_id=reservation.resv_id','left');
		$this->db->where('reservation_room.resv_room_date >=',$date);
		$this->db->where('reservation.deleted','no');
		
		return $this->db->get();
	}
	
	function get_resv_guest($resv_id){
		$this->db->select('*');
		$this->db->from('reservation');
		$this->db->join('guest', 'guest.guest_id=reservation.guest_id', 'left');
		$this->db->where('reservation.resv_id', $resv_id);
		
		return $this->db->get();
	}
	
	function get_resv_room($resv_id){
		$this->db->select('*');
		$this->db->from('reservation');
		$this->db->join('reservation_room', 'reservation_room.resv_id=reservation.resv_id', 'left');
		$this->db->join('room', 'room.room_id=reservation_room.room_id', 'left');
		$this->db->where('reservation.resv_id', $resv_id);
		$this->db->where('reservation_room.deleted', 'no');
		
		return $this->db->get();
	}
	
	function get_service_cost($resv_id) {
		$this->db->select('SUM(serv_ord_total) as cost');
		$this->db->from('service_order');
		$this->db->where('resv_id', $resv_id);
		$this->db->where('deleted', 'no');
		
		return $this->db->get();
	}
	
	function get_room_cost($resv_id) {
		$this->db->select('SUM(price) as cost');
		$this->db->from('reservation_room');
		$this->db->where('resv_id', $resv_id);
		$this->db->where('deleted', 'no');
		
		return $this->db->get();
	}
	
	function get_material_cost($resv_id) {
		$this->db->select('SUM(rm.resv_mat_price*rm.resv_mat_qty) as cost');
		$this->db->from('reservation_room as rr');
		$this->db->join('reservation_material as rm', 'rm.resv_room_id=rr.resv_room_id', 'left');
		$this->db->where('rr.resv_id',$resv_id);
		$this->db->where('rr.deleted', 'no');
		$this->db->where('rm.deleted', 'no');
		
		return $this->db->get();
	}
	
	function get_resv($resv_id) {
		$this->db->select();
		$this->db->from('reservation');
		$this->db->where('resv_id', $resv_id);
		
		return $this->db->get();
	}
	
	function get_payment($resv_id) {
		$this->db->select();
		$this->db->from('reservation_payment as rp');
		$this->db->join('payment_type as pt', 'pt.payment_id=rp.payment_id', 'left');
		$this->db->where('rp.deleted', 'no');
		$this->db->where('rp.resv_id',$resv_id);
		
		return $this->db->get();
	}
	
	function edit_resv($data, $resv_id) {
		$this->db->where('resv_id', $resv_id);
		$this->db->update('reservation', $data);
		
	}
	
	function insert_reservation_material($data) {
		$this->db->insert('reservation_material', $data);
		return $this->db->insert_id();
	}
	
	function select_material($material_id) {
		$this->db->select();
		$this->db->from('material');
		$this->db->where('material_id', $material_id);
		
		return $this->db->get();
	}
	
	function update_reservation_material($resv_mat_id, $data) {
		$this->db->where('resv_mat_id', $resv_mat_id);
		$this->db->update('reservation_material', $data);
		
	}
	
	function is_checked_out($resv_room_id) {
		$this->db->select();
		$this->db->from('reservation_room');
		$this->db->where('resv_room_id', $resv_room_id);
		$this->db->where('resv_status', 'Check Out');
		
		if($this->db->count_all_results() > 0){
			return true;
		}
		else {
			return false;
		}
	}
	
	function set_resv_room($data) {
		$this->db->insert('reservation_room', $data);
		
		return $this->db->insert_id();
	}
	
	function update_resv_room($resv_room_id, $data) {
		$this->db->where('resv_room_id', $resv_room_id);
		$this->db->update('reservation_room', $data);
		
		return $this->db->affected_rows();
	}
	
	function delete_unknown_reservation() {
		$this->db->where('resv_status', 'Booked');
		$this->db->where('resv_id', NULL);
		$this->db->delete('reservation_room');
		
		return $this->db->affected_rows();
	}
	
	function get_std_price($room_id) {
		$this->db->select('rt.room_type_price');
		$this->db->from('room as r');
		$this->db->join('room_type as rt', 'rt.room_type_id=r.room_type_id', 'left');
		$this->db->where('r.room_id', $room_id);
		
		$row = $this->db->get()->row_array();
		
		return $row['room_type_price'];
	}

	function get_rate_price($room_id, $date) {
		$this->db->select('*');
		$this->db->from('room as r');
		$this->db->join('room_type as rt', 'rt.room_type_id=r.room_type_id', 'left');
		$this->db->join('room_rate as rr', 'rr.room_type_id=rt.room_type_id', 'left');
		$this->db->join('rate_type as rat', 'rat.rate_type_id=rr.rate_type_id', 'left');
		$this->db->where('r.room_id', $room_id);
		$this->db->where('rr.room_rate_date', $date);
		
		return $this->db->get();
	}
}